import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
from scipy import stats

# 读取Excel数据
try:
    df = pd.read_excel('员工绩效.xlsx', engine='openpyxl')
    print('当前数据列名:', df.columns.tolist())
except FileNotFoundError:
    print("错误：未找到员工绩效.xlsx 文件")
    exit()

# 分析1：培训时长与测试分数的关系
def analyze_training_duration():
    plt.figure(figsize=(10,6))
    sns.regplot(x='training_hours', y='knowledge_test_score', data=df)
    plt.title('培训时长与知识测试分数关系')
    plt.savefig('training_vs_score.png')
    
    slope, intercept, r_value, p_value, std_err = stats.linregress(df['training_hours'], df['knowledge_test_score'])
    print(f'线性回归分析结果:\nR平方值: {r_value**2:.3f}\nP值: {p_value:.4f}')

# 分析2：工龄与能力指标差异
def analyze_working_years():
    # 计算工龄（假设entry_date是入职日期）
    df['working_years'] = (pd.to_datetime('2024-05-01') - pd.to_datetime(df['entry_date'])).dt.days // 365
    df['exp_group'] = pd.cut(df['working_years'], bins=[0,3,5,10,20], labels=['Junior','Mid-level','Senior','Expert'])
    
    plt.figure(figsize=(12,6))
    sns.boxplot(x='exp_group', y='professional_score', data=df)
    plt.title('Professional Score by Experience Group')
    plt.savefig('working_years_comparison.png')
    
    # ANOVA分析
    groups = [group[1]['professional_score'] for group in df.groupby('exp_group')]
    f_val, p_val = stats.f_oneway(*groups)
    print(f'\nANOVA分析结果:\nF值: {f_val:.2f}\nP值: {p_val:.4f}')

# 分析3：培训前后绩效对比
def analyze_performance_change():
    df['performance_change'] = (df['performance_score'] - df['efficiency_score']) / df['efficiency_score']
    
    plt.figure(figsize=(10,6))
    sns.histplot(df['performance_change'], kde=True)
    plt.title('Performance Change Distribution')
    plt.savefig('performance_change.png')

if __name__ == '__main__':
    analyze_training_duration()
    analyze_working_years()
    analyze_performance_change()
    plt.show()